using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;

namespace ASPNETVN.PORTAL.Modules.ACTP.Components
{
	public partial class EmployeeCertification
	{
		#region Properties.
		
		public int CertificationID { set; get; }
		public int EmployeeID { set; get; }
		public Guid ModuleID { set; get; }
		public DateTime CertifiedDate { set; get; }
		public DateTime ExpiredDate { set; get; }
		public string CreatedBy { set; get; }
		public DateTime CreatedDate { set; get; }
		public string UpdatedBy { set; get; }
		public DateTime UpdatedDate { set; get; }
		
		#endregion
		
		//---------------------------------------------------------------------------------------------
		
		#region Methods
		protected static IList<EmployeeCertification> ConvertToCollection(IDataReader reader)
		{
			IList<EmployeeCertification> collection = new List<EmployeeCertification>();
			while (reader.Read())
			{
				EmployeeCertification entity = new EmployeeCertification();
				if (!reader.IsDBNull(reader.GetOrdinal("CertificationID"))) entity.CertificationID = reader.GetInt32(reader.GetOrdinal("CertificationID"));
				if (!reader.IsDBNull(reader.GetOrdinal("EmployeeID"))) entity.EmployeeID = reader.GetInt32(reader.GetOrdinal("EmployeeID"));
				if (!reader.IsDBNull(reader.GetOrdinal("ModuleID"))) entity.ModuleID = reader.GetGuid(reader.GetOrdinal("ModuleID"));
				if (!reader.IsDBNull(reader.GetOrdinal("CertifiedDate"))) entity.CertifiedDate = reader.GetDateTime(reader.GetOrdinal("CertifiedDate"));
				if (!reader.IsDBNull(reader.GetOrdinal("ExpiredDate"))) entity.ExpiredDate = reader.GetDateTime(reader.GetOrdinal("ExpiredDate"));
				if (!reader.IsDBNull(reader.GetOrdinal("CreatedBy"))) entity.CreatedBy = reader.GetString(reader.GetOrdinal("CreatedBy"));
				if (!reader.IsDBNull(reader.GetOrdinal("CreatedDate"))) entity.CreatedDate = reader.GetDateTime(reader.GetOrdinal("CreatedDate"));
				if (!reader.IsDBNull(reader.GetOrdinal("UpdatedBy"))) entity.UpdatedBy = reader.GetString(reader.GetOrdinal("UpdatedBy"));
				if (!reader.IsDBNull(reader.GetOrdinal("UpdatedDate"))) entity.UpdatedDate = reader.GetDateTime(reader.GetOrdinal("UpdatedDate"));
				collection.Add(entity);
			}
			reader.Close();
			return collection;
		}
		
		#endregion
		
		//---------------------------------------------------------------------------------------------
		
		#region Select methods.
		
		public static EmployeeCertification Load(int certificationID, int employeeID)
		{
			const string spName = "[dbo].[p_ACTP_EmployeeCertification_Load]";
            SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase();
			SqlCommand dbCommand = (SqlCommand) db.GetStoredProcCommand(spName);
			
			db.AddInParameter(dbCommand, "@CertificationID", SqlDbType.Int, certificationID);
			db.AddInParameter(dbCommand, "@EmployeeID", SqlDbType.Int, employeeID);
            IDataReader reader = db.ExecuteReader(dbCommand);
			IList<EmployeeCertification> collection = ConvertToCollection(reader);	
			if (collection.Count > 0)
			{
				return collection[0];
			}
			return null;
		}		
		
		//---------------------------------------------------------------------------------------------
		public static IList<EmployeeCertification> SelectCollectionAll(Guid moduleID)
		{
			IDataReader reader = SelectReaderAll(moduleID);
			return ConvertToCollection(reader);			
		}		
		
		//---------------------------------------------------------------------------------------------
		
		public static IList<EmployeeCertification> SelectCollectionDynamic(string whereCondition, string orderByExpression)
		{
			IDataReader reader = SelectReaderDynamic(whereCondition, orderByExpression);
			return ConvertToCollection(reader);		
		}
		
		//---------------------------------------------------------------------------------------------
		
		// Select by foreign key return collection		
		public static IList<EmployeeCertification> SelectCollectionBy_CertificationID(int certificationID)
		{
            IDataReader reader = SelectReaderBy_CertificationID(certificationID);
			return ConvertToCollection(reader);	
		}		
		//---------------------------------------------------------------------------------------------
		public static IList<EmployeeCertification> SelectCollectionBy_EmployeeID(int employeeID)
		{
            IDataReader reader = SelectReaderBy_EmployeeID(employeeID);
			return ConvertToCollection(reader);	
		}		
		//---------------------------------------------------------------------------------------------
		
		public static DataSet SelectBy_CertificationID(int certificationID)
		{
			const string spName = "[dbo].[p_ACTP_EmployeeCertification_SelectBy_CertificationID]";
            SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand) db.GetStoredProcCommand(spName);
			
			db.AddInParameter(dbCommand, "@CertificationID", SqlDbType.Int, certificationID);
						
            return db.ExecuteDataSet(dbCommand);
		}
		//---------------------------------------------------------------------------------------------
		public static DataSet SelectBy_EmployeeID(int employeeID)
		{
			const string spName = "[dbo].[p_ACTP_EmployeeCertification_SelectBy_EmployeeID]";
            SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand) db.GetStoredProcCommand(spName);
			
			db.AddInParameter(dbCommand, "@EmployeeID", SqlDbType.Int, employeeID);
						
            return db.ExecuteDataSet(dbCommand);
		}
		//---------------------------------------------------------------------------------------------

		public static DataSet SelectAll(Guid moduleID)
        {
            const string spName = "[dbo].[p_ACTP_EmployeeCertification_SelectAll]";
            SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand) db.GetStoredProcCommand(spName);

			db.AddInParameter(dbCommand, "@ModuleID", SqlDbType.UniqueIdentifier, moduleID);
            return db.ExecuteDataSet(dbCommand);
        }
		
		//---------------------------------------------------------------------------------------------
		
		public static DataSet SelectDynamic(string whereCondition, string orderByExpression)
		{
            const string spName = "[dbo].[p_ACTP_EmployeeCertification_SelectDynamic]";
            SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand) db.GetStoredProcCommand(spName);

			db.AddInParameter(dbCommand, "@WhereCondition", SqlDbType.NVarChar, whereCondition);
            db.AddInParameter(dbCommand, "@OrderByExpression", SqlDbType.NVarChar, orderByExpression);
            
            return db.ExecuteDataSet(dbCommand);        				
		}
		
		//---------------------------------------------------------------------------------------------
				
		public static IDataReader SelectReaderAll(Guid moduleID)
        {
            const string spName = "[dbo].[p_ACTP_EmployeeCertification_SelectAll]";
            SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand) db.GetStoredProcCommand(spName);
			db.AddInParameter(dbCommand, "@ModuleID", SqlDbType.UniqueIdentifier, moduleID);
            return db.ExecuteReader(dbCommand);
        }
		
		//---------------------------------------------------------------------------------------------
		
		public static IDataReader SelectReaderDynamic(string whereCondition, string orderByExpression)
		{
            const string spName = "[dbo].[p_ACTP_EmployeeCertification_SelectDynamic]";
            SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand) db.GetStoredProcCommand(spName);

			db.AddInParameter(dbCommand, "@WhereCondition", SqlDbType.NVarChar, whereCondition);
            db.AddInParameter(dbCommand, "@OrderByExpression", SqlDbType.NVarChar, orderByExpression);
            
            return db.ExecuteReader(dbCommand);        				
		}
		
		//---------------------------------------------------------------------------------------------
		
		// Select by foreign key return collection		
		public static IDataReader SelectReaderBy_CertificationID(int certificationID)
		{
			const string spName = "p_ACTP_EmployeeCertification_SelectBy_CertificationID";
            SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase();
			SqlCommand dbCommand = (SqlCommand) db.GetStoredProcCommand(spName);
			
			db.AddInParameter(dbCommand, "@CertificationID", SqlDbType.Int, certificationID);
			
            return db.ExecuteReader(dbCommand);
		}		
		//---------------------------------------------------------------------------------------------
		public static IDataReader SelectReaderBy_EmployeeID(int employeeID)
		{
			const string spName = "p_ACTP_EmployeeCertification_SelectBy_EmployeeID";
            SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase();
			SqlCommand dbCommand = (SqlCommand) db.GetStoredProcCommand(spName);
			
			db.AddInParameter(dbCommand, "@EmployeeID", SqlDbType.Int, employeeID);
			
            return db.ExecuteReader(dbCommand);
		}		
		//---------------------------------------------------------------------------------------------
		
		#endregion
		
		//---------------------------------------------------------------------------------------------
		
		#region Insert methods.
		
		public static int InsertEmployeeCertification(int certificationID, int employeeID, Guid moduleID, DateTime certifiedDate, DateTime expiredDate, string createdBy, DateTime createdDate, string updatedBy, DateTime updatedDate)
		{
			EmployeeCertification entity = new EmployeeCertification();	
			entity.CertificationID = certificationID;
			entity.EmployeeID = employeeID;
			entity.ModuleID = moduleID;
			entity.CertifiedDate = certifiedDate;
			entity.ExpiredDate = expiredDate;
			entity.CreatedBy = createdBy;
			entity.CreatedDate = createdDate;
			entity.UpdatedBy = updatedBy;
			entity.UpdatedDate = updatedDate;
			return entity.Insert();
		}
		
		public int Insert()
		{
			return this.Insert(null);
		}		
		
		//---------------------------------------------------------------------------------------------
		
		public int Insert(SqlTransaction transaction)
		{			
			const string spName = "[dbo].[p_ACTP_EmployeeCertification_Insert]";		
            SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand) db.GetStoredProcCommand(spName);

			db.AddInParameter(dbCommand, "@CertificationID", SqlDbType.Int, CertificationID);
			db.AddInParameter(dbCommand, "@EmployeeID", SqlDbType.Int, EmployeeID);
			db.AddInParameter(dbCommand, "@ModuleID", SqlDbType.UniqueIdentifier, ModuleID);
			db.AddInParameter(dbCommand, "@CertifiedDate", SqlDbType.DateTime, CertifiedDate.Year <= 1753 ? DBNull.Value : (object) CertifiedDate);
			db.AddInParameter(dbCommand, "@ExpiredDate", SqlDbType.DateTime, ExpiredDate.Year <= 1753 ? DBNull.Value : (object) ExpiredDate);
			db.AddInParameter(dbCommand, "@CreatedBy", SqlDbType.VarChar, CreatedBy);
			db.AddInParameter(dbCommand, "@CreatedDate", SqlDbType.DateTime, CreatedDate.Year <= 1753 ? DBNull.Value : (object) CreatedDate);
			db.AddInParameter(dbCommand, "@UpdatedBy", SqlDbType.VarChar, UpdatedBy);
			db.AddInParameter(dbCommand, "@UpdatedDate", SqlDbType.DateTime, UpdatedDate.Year <= 1753 ? DBNull.Value : (object) UpdatedDate);
			
			if (transaction != null)
			{
				return db.ExecuteNonQuery(dbCommand, transaction);
			}
            else
			{
				return db.ExecuteNonQuery(dbCommand);
			}			
		}
				
		//---------------------------------------------------------------------------------------------
		public static bool InsertCollection(IList<EmployeeCertification> collection)
        {
            bool ret;
			SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase();
            using (SqlConnection connection = (SqlConnection)db.CreateConnection())
            {
                connection.Open();
                using(SqlTransaction transaction = connection.BeginTransaction())
				{
					try
					{
						bool ret01 = true;
						foreach (EmployeeCertification item in collection)
						{
							if (item.Insert(transaction) <= 0)
							{							
								ret01 = false;
								break;
							}
						}
						if (ret01)
						{
							transaction.Commit();
							ret = true;
						}
						else
						{
							transaction.Rollback();
							ret = false;                    	
						}
					}
					catch (Exception ex)
					{
						transaction.Rollback();
						throw new Exception("Error at InsertCollection method: " + ex.Message);
					}
					finally 
					{
						connection.Close();
					}
				}
            }
            return ret;		
		}
		
		#endregion
		
		//---------------------------------------------------------------------------------------------
		
		#region Insert / Update methods.
		
		public static int InsertUpdateEmployeeCertification(int certificationID, int employeeID, Guid moduleID, DateTime certifiedDate, DateTime expiredDate, string createdBy, DateTime createdDate, string updatedBy, DateTime updatedDate)
		{
			EmployeeCertification entity = new EmployeeCertification();			
			entity.CertificationID = certificationID;
			entity.EmployeeID = employeeID;
			entity.ModuleID = moduleID;
			entity.CertifiedDate = certifiedDate;
			entity.ExpiredDate = expiredDate;
			entity.CreatedBy = createdBy;
			entity.CreatedDate = createdDate;
			entity.UpdatedBy = updatedBy;
			entity.UpdatedDate = updatedDate;
			return entity.InsertUpdate();
		}
		
		//---------------------------------------------------------------------------------------------
		
		public int InsertUpdate()
		{
			return this.InsertUpdate(null);
		}
		
		//---------------------------------------------------------------------------------------------

		public int InsertUpdate(SqlTransaction transaction)
		{			
			const string spName = "p_ACTP_EmployeeCertification_InsertUpdate";		
            SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase();
			SqlCommand dbCommand = (SqlCommand) db.GetStoredProcCommand(spName);

			db.AddInParameter(dbCommand, "@CertificationID", SqlDbType.Int, CertificationID);
			db.AddInParameter(dbCommand, "@EmployeeID", SqlDbType.Int, EmployeeID);
			db.AddInParameter(dbCommand, "@ModuleID", SqlDbType.UniqueIdentifier, ModuleID);
			db.AddInParameter(dbCommand, "@CertifiedDate", SqlDbType.DateTime, CertifiedDate.Year <= 1753 ? DBNull.Value : (object) CertifiedDate);
			db.AddInParameter(dbCommand, "@ExpiredDate", SqlDbType.DateTime, ExpiredDate.Year <= 1753 ? DBNull.Value : (object) ExpiredDate);
			db.AddInParameter(dbCommand, "@CreatedBy", SqlDbType.VarChar, CreatedBy);
			db.AddInParameter(dbCommand, "@CreatedDate", SqlDbType.DateTime, CreatedDate.Year <= 1753 ? DBNull.Value : (object) CreatedDate);
			db.AddInParameter(dbCommand, "@UpdatedBy", SqlDbType.VarChar, UpdatedBy);
			db.AddInParameter(dbCommand, "@UpdatedDate", SqlDbType.DateTime, UpdatedDate.Year <= 1753 ? DBNull.Value : (object) UpdatedDate);
			
			if (transaction != null)
                return db.ExecuteNonQuery(dbCommand, transaction);
            else
                return db.ExecuteNonQuery(dbCommand);			
		}
		
		//---------------------------------------------------------------------------------------------
		public static bool InsertUpdateCollection(IList<EmployeeCertification> collection)
        {
            bool ret;
			SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase();
            using (SqlConnection connection = (SqlConnection)db.CreateConnection())
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
				{
					try
					{
						bool ret01 = true;
						foreach (EmployeeCertification item in collection)
						{
							if (item.InsertUpdate(transaction) <= 0)
							{
								ret01 = false;
								break;
							}
						}
						if (ret01)
						{
							transaction.Commit();
							ret = true;
						}
						else
						{
							transaction.Rollback();
							ret = false;                    	
						}
					}
					catch (Exception ex)
					{
						transaction.Rollback();
						throw new Exception("Error at InsertUpdateCollection method: " + ex.Message);
						
					}
					finally 
					{
						connection.Close();
					}
				}
            }
            return ret;		
		}	
		#endregion
		
		//---------------------------------------------------------------------------------------------
		
		#region Update methods.
		
		public static int UpdateEmployeeCertification(int certificationID, int employeeID, Guid moduleID, DateTime certifiedDate, DateTime expiredDate, string createdBy, DateTime createdDate, string updatedBy, DateTime updatedDate)
		{
			EmployeeCertification entity = new EmployeeCertification();			
			entity.CertificationID = certificationID;
			entity.EmployeeID = employeeID;
			entity.ModuleID = moduleID;
			entity.CertifiedDate = certifiedDate;
			entity.ExpiredDate = expiredDate;
			entity.CreatedBy = createdBy;
			entity.CreatedDate = createdDate;
			entity.UpdatedBy = updatedBy;
			entity.UpdatedDate = updatedDate;
			return entity.Update();
		}
		
		//---------------------------------------------------------------------------------------------
		
		public int Update()
		{
			return this.Update(null);
		}
		
		//---------------------------------------------------------------------------------------------

		public int Update(SqlTransaction transaction)
		{
			const string spName = "[dbo].[p_ACTP_EmployeeCertification_Update]";		
			SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase();
			SqlCommand dbCommand = (SqlCommand) db.GetStoredProcCommand(spName);

			db.AddInParameter(dbCommand, "@CertificationID", SqlDbType.Int, CertificationID);
			db.AddInParameter(dbCommand, "@EmployeeID", SqlDbType.Int, EmployeeID);
			db.AddInParameter(dbCommand, "@ModuleID", SqlDbType.UniqueIdentifier, ModuleID);
			db.AddInParameter(dbCommand, "@CertifiedDate", SqlDbType.DateTime, CertifiedDate.Year <= 1753 ? DBNull.Value : (object) CertifiedDate);
			db.AddInParameter(dbCommand, "@ExpiredDate", SqlDbType.DateTime, ExpiredDate.Year <= 1753 ? DBNull.Value : (object) ExpiredDate);
			db.AddInParameter(dbCommand, "@CreatedBy", SqlDbType.VarChar, CreatedBy);
			db.AddInParameter(dbCommand, "@CreatedDate", SqlDbType.DateTime, CreatedDate.Year <= 1753 ? DBNull.Value : (object) CreatedDate);
			db.AddInParameter(dbCommand, "@UpdatedBy", SqlDbType.VarChar, UpdatedBy);
			db.AddInParameter(dbCommand, "@UpdatedDate", SqlDbType.DateTime, UpdatedDate.Year <= 1753 ? DBNull.Value : (object) UpdatedDate);
			
			if (transaction != null)
                return db.ExecuteNonQuery(dbCommand, transaction);
            else
                return db.ExecuteNonQuery(dbCommand);
		}
				
		//---------------------------------------------------------------------------------------------
		public static bool UpdateCollection(IList<EmployeeCertification> collection)
        {
            bool ret;
			SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase();
            using (SqlConnection connection = (SqlConnection)db.CreateConnection())
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
				{
					try
					{
						bool ret01 = true;
						foreach (EmployeeCertification item in collection)
						{
							if (item.Update(transaction) <= 0)
							{
								ret01 = false;
								break;
							}
						}
						if (ret01)
						{
							transaction.Commit();
							ret = true;
						}
						else
						{
							transaction.Rollback();
							ret = false;                    	
						}
					}
					catch (Exception ex)
					{
						transaction.Rollback();
						throw new Exception("Error at UpdateCollection method: " + ex.Message);
					}
					finally 
					{
						connection.Close();
					}
				}
            }
            return ret;		
		}
		
		#endregion
		
		//---------------------------------------------------------------------------------------------
		
		#region Delete methods.
		
		public static int DeleteEmployeeCertification(int certificationID, int employeeID)
		{
			EmployeeCertification entity = new EmployeeCertification();
			entity.CertificationID = certificationID;
			entity.EmployeeID = employeeID;
			
			return entity.Delete();
		}
		
		public int Delete()
		{
			return this.Delete(null);
		}
		
		//---------------------------------------------------------------------------------------------

		public int Delete(SqlTransaction transaction)
		{
			const string spName = "[dbo].[p_ACTP_EmployeeCertification_Delete]";		
			SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase();
			SqlCommand dbCommand = (SqlCommand) db.GetStoredProcCommand(spName);
			
			db.AddInParameter(dbCommand, "@CertificationID", SqlDbType.Int, CertificationID);
			db.AddInParameter(dbCommand, "@EmployeeID", SqlDbType.Int, EmployeeID);
			
			if (transaction != null)
                return db.ExecuteNonQuery(dbCommand, transaction);
            else
                return db.ExecuteNonQuery(dbCommand);
		}
		
		//---------------------------------------------------------------------------------------------
		
		public static int DeleteBy_CertificationID(int certificationID)
		{
			const string spName = "[dbo].[p_ACTP_EmployeeCertification_DeleteBy_CertificationID]";
            SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand) db.GetStoredProcCommand(spName);
			
			db.AddInParameter(dbCommand, "@CertificationID", SqlDbType.Int, certificationID);
						
            return db.ExecuteNonQuery(dbCommand);
		}
		
		//---------------------------------------------------------------------------------------------
			
		public static int DeleteBy_EmployeeID(int employeeID)
		{
			const string spName = "[dbo].[p_ACTP_EmployeeCertification_DeleteBy_EmployeeID]";
            SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand) db.GetStoredProcCommand(spName);
			
			db.AddInParameter(dbCommand, "@EmployeeID", SqlDbType.Int, employeeID);
						
            return db.ExecuteNonQuery(dbCommand);
		}
		
		//---------------------------------------------------------------------------------------------
			
		
		public static int DeleteDynamic(string whereCondition)
		{
			const string spName = "[dbo].[p_ACTP_EmployeeCertification_DeleteDynamic]";
            SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand) db.GetStoredProcCommand(spName);

			db.AddInParameter(dbCommand, "@WhereCondition", SqlDbType.NVarChar, whereCondition);
            
            return db.ExecuteNonQuery(dbCommand);   
		}
		//---------------------------------------------------------------------------------------------
		
		public static bool DeleteCollection(IList<EmployeeCertification> collection)
        {
            bool ret;
			SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase();
            using (SqlConnection connection = (SqlConnection)db.CreateConnection())
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
				{
					try
					{
						bool ret01 = true;
						foreach (EmployeeCertification item in collection)
						{
							if (item.Delete(transaction) <= 0)
							{
								ret01 = false;
								break;
							}
						}
						if (ret01)
						{
							transaction.Commit();
							ret = true;
						}
						else
						{
							transaction.Rollback();
							ret = false;                    	
						}
					}
					catch (Exception ex)
					{
						transaction.Rollback();
						throw new Exception("Error at DeleteCollection method: " + ex.Message);
					}
					finally 
					{
						connection.Close();
					}
				}
            }
            return ret;		
		}
		#endregion
	}	
}